# -*- coding: UTF-8  -*-
import commons.util

import commons.dbutil as db
import pgdb
import commons.util as util

#----------------------------对数据库操作的四种基本方法-----------------------------------#

def create(newDiary):

    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    T=conn.cursor()

    title = newDiary.title
    content = newDiary.content
    create_time = newDiary.create_time

    C="INSERT INTO diary(title,content,create_time) VALUES(%s, %s, %s)"
    T.execute(C, [title,content,create_time])
    
    conn.commit()
    conn.close()


##调用create()
myNewDiary = util.getObj();
myNewDiary.title = "title only"
myNewDiary.content = "some contents..."
myNewDiary.create_time = '2009-12-11 12:33:30'
create(myNewDiary)

def update_by_id(newDiary):

    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    U=conn.cursor()

    content=newDiary.content;create_time=newDiary.create_time;title=newDiary.title;id=newDiary.id
    C="UPDATE diary SET content=%s,create_time=%s,title=%s WHERE id=%s"
    U.execute(C,[content,create_time,title,id])

    conn.commit()
    conn.close()


nDiary=util.getObj()
nDiary.content="new changs"
nDiary.title="NEW"
nDiary.create_time='now()'
nDiary.id="5"
#update_by_id(nDiary)

def delete_by_id(diaryID):
        conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
        D=conn.cursor()

        id=diaryID
        C="DELETE FROM diary WHERE id=%s"
        D.execute(C,[id])

        conn.commit()
        conn.close()
#dDiary=util.getObj()
#dDiary=int(raw_input("please enter a digit:"))
#delete_by_id(dDiary)

def read_by_id(diaryID):
    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    R=conn.cursor()

    id=diaryID
    C="SELECT content,id,title,create_time FROM diary WHERE id=%s"
    R.execute(C,[id])
    P=R.fetchall()
    conn.commit()
    conn.close()

    resultList=[];
    for row in P:
        newlist=util.getObj();
        newlist.content=row[0]
        newlist.id=row[1]
        newlist.title=row[2]
        newlist.create_time=row[3]
        resultList.append(newlist)

    for y in resultList:
        return y.content,y.id,y.title,y.create_time

#rDiary=util.getObj()
#rDiary=int(raw_input("please enter a digit:"))
#read_by_id(rDiary)



def query_by_latest(limitNum):
    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    Q=conn.cursor()

    int=limitNum
    C="SELECT content,id,title FROM diary ORDER BY create_time DESC LIMIT %d"
    Q.execute(C,[int])

    P=Q.fetchall()
    conn.commit()
    resultsList = [];
    for each_row in P:

        aDiary=util.getObj()
        aDiary.content=each_row[0]
        aDiary.id=each_row[1]
        aDiary.title=each_row[2]
        resultsList.append(aDiary)

    for x in resultsList:
        return x.content,x.id, x.title
    
#lDiary=util.getObj()
#lDiary=int(raw_input("please enter a digit:"))
#query_by_latest(lDiary)


#-----------------------------------------------------------------------------------------------
def INSERT():
    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    T=conn.cursor()
    C="INSERT INTO diary(title,content,create_time) VALUES('the first note','hello pg',now())"
    T.execute(C)
    conn.commit()
    conn.close()
def SELECT():
    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    P=conn.cursor()
    C='''SELECT * FROM diary
                ORDER BY id DESC LIMIT 5'''
    P.execute(C)
    D=P.fetchall()
    
    conn.commit()
    conn.close()
    return D
def UPDATE():
    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    Y=conn.cursor()
    C="UPDATE diary SET content='hello world' where id>2 and id<=10"
    Y.execute(C)
    conn.commit()
    conn.close()
def DELETE():
    conn=pgdb.connect(user="postgres", password='0573', host='localhost:5432',database="diary_db")
    Z=conn.cursor()
    C="DELETE FROM diary WHERE id=1"
    Z.execute(C)
    conn.commit()
    conn.close()

## ----------- 以下为 dbutil方式的操作， 仅作对比！


#------------------------定义开始---------->

# 1.连接数据库的参数
conn_cfg =  {    "host":    "localhost:5432",
                "user":     "postgres",
                "passwd":   "0573",
                "db":       "diary_db"
            }

# 2.表结构说明
tb_diary =  {   "name":     "diary",                            # 表名
                "fields":   "id title content create_time read_count",     # 字段列表
                "idseq":    "diary_id_seq",                       # 自增ID序列名
                "format":   "dsssd",                             # 各字段的类型 (d表示int, s表示string)
                "defaults": ( 0 ,'' ,'' ,'',0 )                   # 各字段的默认值
            }
            
#------------------------定义结束----------<


# dbutil方式 插入新数据的方法
def add_newdiary():
    conn = db.getconn(conn_cfg) # 取得db连接
    
    oneDiary = util.getObj() # 构建新数据对象
    oneDiary.title = "a title"
    oneDiary.content = "some contents."
    oneDiary.create_time ='now()'

    db.create(conn, tb_diary, oneDiary) #执行
    db.submit(conn)  #提交并关闭db连接


#add_newdiary()  # 方法测试


def create_u(newDiary):
    conn=db.getconn(conn_cfg)

    aDiary=util.getObj()
    aDiary.title=newDiary.title
    aDiary.content=newDiary.content
    aDiary.create_time='now()'

    db.create(conn, tb_diary, aDiary)

    db.submit(conn)


def updateById(newDiary):
    conn=db.getconn(conn_cfg)

    aDiary=util.getObj()
    aDiary.id=newDiary.id
    aDiary.title=newDiary.title
    aDiary.content=newDiary.content
    aDiary.create_time='now()'

    db.save(conn,tb_diary,aDiary)

    db.submit(conn)


def deleteById(id):
    conn=db.getconn(conn_cfg)

    db.delete(conn, tb_diary, id)

    db.submit(conn)


def readById(id):
    conn=db.getconn(conn_cfg)

    obj=db.read(conn, tb_diary,id)

    db.submit(conn)

    return obj


def queryByLatest(numLimit):

    SQL='''SELECT id,title,content,create_time FROM diary ORDER BY id DESC LIMIT %d'''

    conn=db.getconn(conn_cfg)

    results=db.query(conn, SQL, params=[numLimit], fields="id title content create_time")
    
    db.submit(conn)
    
    return results

#-------------得到表中所有数据----------------




def queryId():
    SQL="SELECT id FROM diary"

    conn=db.getconn(conn_cfg)

    results=db.query(conn, SQL, fields="id")

    db.submit(conn)

    return results




print util.getObj()